﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class LotAttributeDA : ILotAttributeDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string EXPIRY_DATE = "@EXPIRY_DATE";
        private string EXTERNAL_LOT = "@EXTERNAL_LOT";
        private string LOT = "@LOT";
        private string LOT_ATTR01 = "@LOT_ATTR01";
        private string LOT_ATTR02 = "@LOT_ATTR02";
        private string LOT_ATTR03 = "@LOT_ATTR03";
        private string LOT_ATTR04 = "@LOT_ATTR04";
        private string LOT_ATTR05 = "@LOT_ATTR05";
        private string LOT_ATTR06 = "@LOT_ATTR06";
        private string LOT_ATTR07 = "@LOT_ATTR07";
        private string LOT_ATTR08 = "@LOT_ATTR08";
        private string LOT_ATTR09 = "@LOT_ATTR09";
        private string LOT_ATTR10 = "@LOT_ATTR10";
        private string OWNER_ID = "@OWNER_ID";
        private string PRODUCE_DATE = "@PRODUCE_DATE";
        private string RECEIPT_DATE = "@RECEIPT_DATE";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_IS_HAS_SAME_LOT = " SELECT LOT FROM @_@LOT_ATTRIBUTE WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID ";
        private string SQL_CHECK_LOT_ATTRIBUTE_ID_UNIQUE = " SELECT COUNT(1) FROM @_@LOT_ATTRIBUTE WHERE LOT = @LOT ";
        private string SQL_DELETE_LOT_ATTRIBUTE = " DELETE FROM @_@LOT_ATTRIBUTE WHERE LOT = @LOT ";
        private string SQL_GET_INV_LOT_BY_LOTATTRIBUTE_4_PREALLOC = " SELECT IL.ROWID, IL.WH_ID, IL.LOT, IL.OWNER_ID, IL.SKU_ID, IL.CASE_CNT, IL.INNER_PACK, IL.QTY, IL.PALLET, IL.CUBE, IL.GROSS_WGT, IL.NET_WGT, IL.OTHER_UNIT1,  IL.OTHER_UNIT2, IL.PREALLOCATED_QTY, IL.ALLOCATED_QTY, IL.PICKED_QTY, IL.ONHOLD_QTY, IL.STATUS, IL.ARCHIVE_QTY, IL.ARCHIVE_DATE, IL.PREALLOCATED_GROSS_WGT,  IL.PREALLOCATED_NET_WGT, IL.ALLOCATED_GROSS_WGT, IL.ALLOCATED_NET_WGT, IL.PICKED_GROSS_WGT, IL.PICKED_NET_WGT, IL.TS, IL.CREATED_BY, IL.CREATED_DATE,  IL.UPDATED_BY, IL.UPDATED_DATE,IL.UNIT_PRICE FROM @_@LOT_ATTRIBUTE LA LEFT JOIN @_@INV_LOT IL ON IL.LOT = LA.LOT WHERE LA.OWNER_ID = @OWNER_ID AND LA.SKU_ID = @SKU_ID AND IL.QTY > 0 AND IL.QTY - IL.PREALLOCATED_QTY - IL.ALLOCATED_QTY - IL.PICKED_QTY - IL.ONHOLD_QTY > 0 AND IL.STATUS = 'OK' ";
        private string SQL_INSERT_LOT_ATTRIBUTE = " INSERT INTO @_@LOT_ATTRIBUTE ( WH_ID, OWNER_ID, SKU_ID, LOT, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, VENDOR_ID, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @OWNER_ID, @SKU_ID, @LOT, @LOT_ATTR01, @LOT_ATTR02, @LOT_ATTR03, @LOT_ATTR04, @LOT_ATTR05, @LOT_ATTR06, @LOT_ATTR07, @LOT_ATTR08, @LOT_ATTR09, @LOT_ATTR10, @PRODUCE_DATE, @EXPIRY_DATE, @RECEIPT_DATE, @EXTERNAL_LOT,@VENDOR_ID, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_LOT_ATTRIBUTE = " SELECT  L.ROWID, L.WH_ID, L.OWNER_ID, L.SKU_ID, S.DESCR AS SKU_DESCR, L.LOT, L.LOT_ATTR01, L.LOT_ATTR02, L.LOT_ATTR03, L.LOT_ATTR04, L.LOT_ATTR05, L.LOT_ATTR06, L.LOT_ATTR07, L.LOT_ATTR08, L.LOT_ATTR09, L.LOT_ATTR10, L.PRODUCE_DATE, L.EXPIRY_DATE, L.RECEIPT_DATE, L.EXTERNAL_LOT,L.VENDOR_ID,L.TS, L.CREATED_BY, L.CREATED_DATE, L.UPDATED_BY, L.UPDATED_DATE  FROM  @_@LOT_ATTRIBUTE L LEFT JOIN @_@SKU S ON L.OWNER_ID = S.OWNER_ID AND L.SKU_ID = S.SKU_ID  ";
        private string SQL_SELECT_LOT_ATTRIBUTE_BY_SKU_ID_OWNER_ID = "  SELECT  L.ROWID, L.WH_ID, L.OWNER_ID, L.SKU_ID,  L.LOT, L.LOT_ATTR01, L.LOT_ATTR02, L.LOT_ATTR03, L.LOT_ATTR04, L.LOT_ATTR05, L.LOT_ATTR06, L.LOT_ATTR07, L.LOT_ATTR08, L.LOT_ATTR09, L.LOT_ATTR10, L.PRODUCE_DATE, L.EXPIRY_DATE, L.RECEIPT_DATE, L.EXTERNAL_LOT,L.VENDOR_ID,L.TS, L.CREATED_BY, L.CREATED_DATE, L.UPDATED_BY, L.UPDATED_DATE  FROM  @_@LOT_ATTRIBUTE L LEFT JOIN @_@INV_LOT IL ON L.LOT=IL.LOT  ";
        private string SQL_UPDATE_LOT_ATTRIBUTE = " UPDATE @_@LOT_ATTRIBUTE SET WH_ID = @WH_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, LOT_ATTR01 = @LOT_ATTR01, LOT_ATTR02 = @LOT_ATTR02, LOT_ATTR03 = @LOT_ATTR03, LOT_ATTR04 = @LOT_ATTR04, LOT_ATTR05 = @LOT_ATTR05, LOT_ATTR06 = @LOT_ATTR06, LOT_ATTR07 = @LOT_ATTR07, LOT_ATTR08 = @LOT_ATTR08, LOT_ATTR09 = @LOT_ATTR09, LOT_ATTR10 = @LOT_ATTR10, PRODUCE_DATE = @PRODUCE_DATE, EXPIRY_DATE = @EXPIRY_DATE, RECEIPT_DATE = @RECEIPT_DATE, EXTERNAL_LOT = @EXTERNAL_LOT,VENDOR_ID = @VENDOR_ID, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE LOT = @LOT  ";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string WH_ID = "@WH_ID";

        public string CheckIsHasSameLot(ReceiptLotInfo receiptLotInfo, string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_CHECK_IS_HAS_SAME_LOT;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.OWNER_ID, ownerID), new SqlParameter(this.SKU_ID, skuID) };
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr01))
            {
                sql = sql + " AND LOT_ATTR01 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr01);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr02))
            {
                sql = sql + " AND LOT_ATTR02 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr02);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr03))
            {
                sql = sql + " AND LOT_ATTR03 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr03);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr04))
            {
                sql = sql + " AND LOT_ATTR04 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr04);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr05))
            {
                sql = sql + " AND LOT_ATTR05 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr05);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr06))
            {
                sql = sql + " AND LOT_ATTR06 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr06);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr07))
            {
                sql = sql + " AND LOT_ATTR07 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr07);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr08))
            {
                sql = sql + " AND LOT_ATTR08 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr08);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr09))
            {
                sql = sql + " AND LOT_ATTR09 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr09);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr10))
            {
                sql = sql + " AND LOT_ATTR10 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr10);
            }
            DateTime CS00003 = new DateTime();
            if (receiptLotInfo.ProduceDate != CS00003)
            {
                CS00003 = receiptLotInfo.ProduceDate;
                sql = sql + " AND PRODUCE_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            CS00003 = new DateTime();
            if (receiptLotInfo.ExpiryDate != CS00003)
            {
                CS00003 = receiptLotInfo.ExpiryDate;
                sql = sql + " AND EXPIRY_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.ExternalLot))
            {
                sql = sql + " AND EXTERNAL_LOT = " + StrUtils.FormatSQLStr(receiptLotInfo.ExternalLot);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.VendorID))
            {
                sql = sql + " AND VENDOR_ID = " + StrUtils.FormatSQLStr(receiptLotInfo.VendorID);
            }
            if (receiptLotInfo.ReceiptDate != new DateTime())
            {
                sql = sql + " AND CAST(CONVERT(VARCHAR(10),RECEIPT_DATE,23) AS DATETIME) = CAST(CONVERT(VARCHAR(10)," + StrUtils.FormatSQLStr(receiptLotInfo.ReceiptDate.ToShortDateString()) + ",23) AS DATETIME)";
            }
            object lotID = DBHelper.ExecuteScalar(CommandType.Text, sql.Replace("@_@", whLoginID), paras);
            if (lotID != null)
            {
                return lotID.ToString();
            }
            return "";
        }

        public string CheckIsHasSameLot(DataBase dataBase, DbTransaction tran, ReceiptLotInfo receiptLotInfo, string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_CHECK_IS_HAS_SAME_LOT;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.OWNER_ID, ownerID), new SqlParameter(this.SKU_ID, skuID) };
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr01))
            {
                sql = sql + " AND LOT_ATTR01 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr01);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr02))
            {
                sql = sql + " AND LOT_ATTR02 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr02);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr03))
            {
                sql = sql + " AND LOT_ATTR03 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr03);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr04))
            {
                sql = sql + " AND LOT_ATTR04 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr04);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr05))
            {
                sql = sql + " AND LOT_ATTR05 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr05);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr06))
            {
                sql = sql + " AND LOT_ATTR06 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr06);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr07))
            {
                sql = sql + " AND LOT_ATTR07 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr07);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr08))
            {
                sql = sql + " AND LOT_ATTR08 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr08);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr09))
            {
                sql = sql + " AND LOT_ATTR09 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr09);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.LotAttr10))
            {
                sql = sql + " AND LOT_ATTR10 = " + StrUtils.FormatSQLStr(receiptLotInfo.LotAttr10);
            }
            DateTime CS00003 = new DateTime();
            if (receiptLotInfo.ProduceDate != CS00003)
            {
                CS00003 = receiptLotInfo.ProduceDate;
                sql = sql + " AND PRODUCE_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            CS00003 = new DateTime();
            if (receiptLotInfo.ExpiryDate != CS00003)
            {
                CS00003 = receiptLotInfo.ExpiryDate;
                sql = sql + " AND EXPIRY_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.ExternalLot))
            {
                sql = sql + " AND EXTERNAL_LOT = " + StrUtils.FormatSQLStr(receiptLotInfo.ExternalLot);
            }
            if (!string.IsNullOrEmpty(receiptLotInfo.VendorID))
            {
                sql = sql + " AND VENDOR_ID = " + StrUtils.FormatSQLStr(receiptLotInfo.VendorID);
            }
            if (receiptLotInfo.ReceiptDate != new DateTime())
            {
                sql = sql + " AND CAST(CONVERT(VARCHAR(10),RECEIPT_DATE,23) AS DATETIME) = CAST(CONVERT(VARCHAR(10)," + StrUtils.FormatSQLStr(receiptLotInfo.ReceiptDate.ToShortDateString()) + ",23) AS DATETIME)";
            }
            object lotID = dataBase.ExecuteScalar(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
            if (lotID != null)
            {
                return lotID.ToString();
            }
            return "";
        }

        public bool CheckLotAttributeIDUnique(string lotAttributeID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotAttributeID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_LOT_ATTRIBUTE_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckLotAttributeIDUnique(DataBase dataBase, DbTransaction tran, string lotAttributeID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotAttributeID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_LOT_ATTRIBUTE_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteLotAttribute(List<string> lotAttributeID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < lotAttributeID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotAttributeID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_LOT_ATTRIBUTE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteLotAttribute(DataBase dataBase, DbTransaction tran, List<string> lotAttributeID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < lotAttributeID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotAttributeID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_LOT_ATTRIBUTE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllLotAttribute(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOT_ATTRIBUTE.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllLotAttribute(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOT_ATTRIBUTE.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<LotAttributeInfo> GetInvLotAttributeByID(string onwerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_LOT_ATTRIBUTE_BY_SKU_ID_OWNER_ID.Replace("@_@", whLoginID) + " WHERE L.OWNER_ID = @OWNER_ID AND L.SKU_ID=@SKU_ID AND IL.QTY>0  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", onwerID), new SqlParameter("@SKU_ID", skuID) };
            List<LotAttributeInfo> lotAttribute = new List<LotAttributeInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    LotAttributeInfo lotAttributeInfo = new LotAttributeInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), "", reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["VENDOR_ID"].ToString());
                    lotAttribute.Add(lotAttributeInfo);
                }
            }
            return lotAttribute;
        }

        public LotAttributeInfo GetLotAttributeByID(string lotattributeID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOT_ATTRIBUTE.Replace("@_@", whLoginID) + " WHERE LOT = @LOT  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotattributeID) };
            LotAttributeInfo lotAttributeInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    lotAttributeInfo = new LotAttributeInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["VENDOR_ID"].ToString());
                }
            }
            return lotAttributeInfo;
        }

        public LotAttributeInfo GetLotAttributeByID(DataBase dataBase, DbTransaction tran, string lotattributeID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_LOT_ATTRIBUTE.Replace("@_@", whLoginID) + " WHERE LOT = @LOT  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lotattributeID) };
            LotAttributeInfo lotAttributeInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                lotAttributeInfo = new LotAttributeInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["VENDOR_ID"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return lotAttributeInfo;
        }

        public DataSet GetLotAttributeByQueryList(List<string> sqlWhere, LotAttributeQueryEntity lotattributeQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_LOT_ATTRIBUTE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!lotattributeQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, lotattributeQuery.CurrentPage, lotattributeQuery.PageSize, lotattributeQuery.SortField, lotattributeQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetLotAttributeByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, LotAttributeQueryEntity lotattributeQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_LOT_ATTRIBUTE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!lotattributeQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, lotattributeQuery.CurrentPage, lotattributeQuery.PageSize, lotattributeQuery.SortField, lotattributeQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<InvLotInfo> GetLotIDByLotAttribute4PreAllocate(LotAttributeInfo lotAttribute, string rotation, string whLoginID)
        {
            string sql = this.SQL_GET_INV_LOT_BY_LOTATTRIBUTE_4_PREALLOC;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.OWNER_ID, lotAttribute.OwnerID), new SqlParameter(this.SKU_ID, lotAttribute.SkuID) };
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr01))
            {
                sql = sql + " AND LA.LOT_ATTR01 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr01);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr02))
            {
                sql = sql + " AND LA.LOT_ATTR02 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr02);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr03))
            {
                sql = sql + " AND LA.LOT_ATTR03 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr03);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr04))
            {
                sql = sql + " AND LA.LOT_ATTR04 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr04);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr05))
            {
                sql = sql + " AND LA.LOT_ATTR05 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr05);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr06))
            {
                sql = sql + " AND LA.LOT_ATTR06 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr06);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr07))
            {
                sql = sql + " AND LA.LOT_ATTR07 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr07);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr08))
            {
                sql = sql + " AND LA.LOT_ATTR08 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr08);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr09))
            {
                sql = sql + " AND LA.LOT_ATTR09 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr09);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr10))
            {
                sql = sql + " AND LA.LOT_ATTR10 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr10);
            }
            DateTime CS00003 = new DateTime();
            if (lotAttribute.ProduceDate != CS00003)
            {
                CS00003 = lotAttribute.ProduceDate;
                sql = sql + " AND LA.PRODUCE_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            CS00003 = new DateTime();
            if (lotAttribute.ExpiryDate != CS00003)
            {
                CS00003 = lotAttribute.ExpiryDate;
                sql = sql + " AND LA.EXPIRY_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            if (!string.IsNullOrEmpty(lotAttribute.ExternalLot))
            {
                sql = sql + " AND LA.EXTERNAL_LOT = " + StrUtils.FormatSQLStr(lotAttribute.ExternalLot);
            }
            if (!string.IsNullOrEmpty(lotAttribute.VendorID))
            {
                sql = sql + " AND LA.VENDOR_ID = " + StrUtils.FormatSQLStr(lotAttribute.VendorID);
            }
            CS00003 = new DateTime();
            if (lotAttribute.ReceiptDate != CS00003)
            {
                CS00003 = lotAttribute.ReceiptDate;
                sql = sql + " AND CAST(CONVERT(VARCHAR(10),LA.RECEIPT_DATE,23) AS DATETIME) = CAST(CONVERT(VARCHAR(10)," + StrUtils.FormatSQLStr(CS00003.ToShortDateString()) + ",23) AS DATETIME)";
            }
            sql = sql + rotation;
            List<InvLotInfo> invLotInfos = new List<InvLotInfo>();
            IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@_@", whLoginID), paras);
            while (reader.Read())
            {
                InvLotInfo invLotInfo = null;
                invLotInfo = new InvLotInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? ((double) float.Parse(reader["CUBE"].ToString())) : ((double) 0f), (reader["GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["GROSS_WGT"].ToString())) : ((double) 0f), (reader["NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["NET_WGT"].ToString())) : ((double) 0f), (reader["OTHER_UNIT1"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT1"].ToString())) : ((double) 0f), (reader["OTHER_UNIT2"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT2"].ToString())) : ((double) 0f), (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["ONHOLD_QTY"].ToString() != "") ? decimal.Parse(reader["ONHOLD_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["PREALLOCATED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["PREALLOCATED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["PREALLOCATED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["PREALLOCATED_NET_WGT"].ToString())) : ((double) 0f), (reader["ALLOCATED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["ALLOCATED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["ALLOCATED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["ALLOCATED_NET_WGT"].ToString())) : ((double) 0f), (reader["PICKED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["PICKED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["PICKED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["PICKED_NET_WGT"].ToString())) : ((double) 0f), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f);
                invLotInfos.Add(invLotInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invLotInfos;
        }

        public List<InvLotInfo> GetLotIDByLotAttribute4PreAllocate(DataBase dataBase, DbTransaction tran, LotAttributeInfo lotAttribute, string rotation, string whLoginID)
        {
            string sql = this.SQL_GET_INV_LOT_BY_LOTATTRIBUTE_4_PREALLOC;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter(this.OWNER_ID, lotAttribute.OwnerID), new SqlParameter(this.SKU_ID, lotAttribute.SkuID) };
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr01))
            {
                sql = sql + " AND LA.LOT_ATTR01 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr01);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr02))
            {
                sql = sql + " AND LA.LOT_ATTR02 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr02);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr03))
            {
                sql = sql + " AND LA.LOT_ATTR03 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr03);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr04))
            {
                sql = sql + " AND LA.LOT_ATTR04 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr04);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr05))
            {
                sql = sql + " AND LA.LOT_ATTR05 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr05);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr06))
            {
                sql = sql + " AND LA.LOT_ATTR06 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr06);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr07))
            {
                sql = sql + " AND LA.LOT_ATTR07 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr07);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr08))
            {
                sql = sql + " AND LA.LOT_ATTR08 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr08);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr09))
            {
                sql = sql + " AND LA.LOT_ATTR09 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr09);
            }
            if (!string.IsNullOrEmpty(lotAttribute.LotAttr10))
            {
                sql = sql + " AND LA.LOT_ATTR10 = " + StrUtils.FormatSQLStr(lotAttribute.LotAttr10);
            }
            DateTime CS00003 = new DateTime();
            if (lotAttribute.ProduceDate != CS00003)
            {
                CS00003 = lotAttribute.ProduceDate;
                sql = sql + " AND LA.PRODUCE_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            CS00003 = new DateTime();
            if (lotAttribute.ExpiryDate != CS00003)
            {
                CS00003 = lotAttribute.ExpiryDate;
                sql = sql + " AND LA.EXPIRY_DATE = " + StrUtils.FormatSQLStr(CS00003.ToString());
            }
            if (!string.IsNullOrEmpty(lotAttribute.ExternalLot))
            {
                sql = sql + " AND LA.EXTERNAL_LOT = " + StrUtils.FormatSQLStr(lotAttribute.ExternalLot);
            }
            if (!string.IsNullOrEmpty(lotAttribute.VendorID))
            {
                sql = sql + " AND LA.VENDOR_ID = " + StrUtils.FormatSQLStr(lotAttribute.VendorID);
            }
            CS00003 = new DateTime();
            if (lotAttribute.ReceiptDate != CS00003)
            {
                CS00003 = lotAttribute.ReceiptDate;
                sql = sql + " AND CAST(CONVERT(VARCHAR(10),LA.RECEIPT_DATE,23) AS DATETIME) = CAST(CONVERT(VARCHAR(10)," + StrUtils.FormatSQLStr(CS00003.ToShortDateString()) + ",23) AS DATETIME)";
            }
            sql = sql + rotation;
            List<InvLotInfo> invLotInfos = new List<InvLotInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
            while (reader.Read())
            {
                InvLotInfo invLotInfo = null;
                invLotInfo = new InvLotInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? ((double) float.Parse(reader["CUBE"].ToString())) : ((double) 0f), (reader["GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["GROSS_WGT"].ToString())) : ((double) 0f), (reader["NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["NET_WGT"].ToString())) : ((double) 0f), (reader["OTHER_UNIT1"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT1"].ToString())) : ((double) 0f), (reader["OTHER_UNIT2"].ToString() != "") ? ((double) float.Parse(reader["OTHER_UNIT2"].ToString())) : ((double) 0f), (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["ONHOLD_QTY"].ToString() != "") ? decimal.Parse(reader["ONHOLD_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["PREALLOCATED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["PREALLOCATED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["PREALLOCATED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["PREALLOCATED_NET_WGT"].ToString())) : ((double) 0f), (reader["ALLOCATED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["ALLOCATED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["ALLOCATED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["ALLOCATED_NET_WGT"].ToString())) : ((double) 0f), (reader["PICKED_GROSS_WGT"].ToString() != "") ? ((double) float.Parse(reader["PICKED_GROSS_WGT"].ToString())) : ((double) 0f), (reader["PICKED_NET_WGT"].ToString() != "") ? ((double) float.Parse(reader["PICKED_NET_WGT"].ToString())) : ((double) 0f), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f);
                invLotInfos.Add(invLotInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invLotInfos;
        }

        public int InsertLotAttribute(LotAttributeInfo lotAttributeInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                lotAttributeInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_LotAttribute_Parameters(lotAttributeInfo);
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_INSERT_LOT_ATTRIBUTE.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertLotAttribute(DataBase dataBase, DbTransaction tran, LotAttributeInfo lotAttributeInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                lotAttributeInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_LotAttribute_Parameters(lotAttributeInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_LOT_ATTRIBUTE.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_LotAttribute_Parameters(LotAttributeInfo lotAttributeInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 10), new SqlParameter(this.LOT_ATTR01, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR02, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR03, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR04, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR05, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR06, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR07, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR08, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR09, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR10, SqlDbType.VarChar, 50), new SqlParameter(this.PRODUCE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPIRY_DATE, SqlDbType.DateTime, 8), 
                new SqlParameter(this.RECEIPT_DATE, SqlDbType.SmallDateTime, 4), new SqlParameter(this.EXTERNAL_LOT, SqlDbType.VarChar, 100), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            paramArray[0].Value = lotAttributeInfo.WhID.ToString();
            paramArray[1].Value = lotAttributeInfo.OwnerID.ToString();
            paramArray[2].Value = lotAttributeInfo.SkuID.ToString();
            paramArray[3].Value = lotAttributeInfo.Lot.ToString();
            paramArray[4].Value = lotAttributeInfo.LotAttr01.ToString();
            paramArray[5].Value = lotAttributeInfo.LotAttr02.ToString();
            paramArray[6].Value = lotAttributeInfo.LotAttr03.ToString();
            paramArray[7].Value = lotAttributeInfo.LotAttr04.ToString();
            paramArray[8].Value = lotAttributeInfo.LotAttr05.ToString();
            paramArray[9].Value = lotAttributeInfo.LotAttr06.ToString();
            paramArray[10].Value = lotAttributeInfo.LotAttr07.ToString();
            paramArray[11].Value = lotAttributeInfo.LotAttr08.ToString();
            paramArray[12].Value = lotAttributeInfo.LotAttr09.ToString();
            paramArray[13].Value = lotAttributeInfo.LotAttr10.ToString();
            DateTime CS00002 = new DateTime();
            if (lotAttributeInfo.ProduceDate != CS00002)
            {
                paramArray[14].Value = lotAttributeInfo.ProduceDate;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (lotAttributeInfo.ExpiryDate != new DateTime())
            {
                paramArray[15].Value = lotAttributeInfo.ExpiryDate;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            paramArray[0x10].Value = lotAttributeInfo.ReceiptDate;
            if (!string.IsNullOrEmpty(lotAttributeInfo.ExternalLot))
            {
                paramArray[0x11].Value = lotAttributeInfo.ExternalLot;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(lotAttributeInfo.VendorID))
            {
                paramArray[0x12].Value = lotAttributeInfo.VendorID;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            paramArray[0x13].Value = lotAttributeInfo.CreatedBy;
            paramArray[20].Value = lotAttributeInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateLotAttribute(LotAttributeInfo lotAttributeInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    lotAttributeInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_LotAttribute_Parameters(lotAttributeInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_LOT_ATTRIBUTE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateLotAttribute(DataBase dataBase, DbTransaction tran, LotAttributeInfo lotAttributeInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    lotAttributeInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_LotAttribute_Parameters(lotAttributeInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_LOT_ATTRIBUTE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

